﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JJY.DBUtility;
using NBA.Entity;
using NBA.Utility;
using System.Data.SqlClient;
using NBA.DBUtility;

namespace NBA.DataAccess
{
    public class DaMatchInfo
    {
        NbaDbUtility nbaDbUtility = new NbaDbUtility();
        
        public List<MatchInfo> GetMatchListByDate(DateTime dateTime)
        {
            string sql = string.Format("SELECT * FROM MatchInfo " +
                " WHERE DATEDIFF(DAY,MatchDateTime,'{0}') = 0 ORDER BY MatchDateTime ASC", dateTime);
            return NbaEntityHelper<MatchInfo>.GetEntitySet(sql);
        }

        public List<MatchInfo> GetMatchListByTeamId(Guid teamId, int matchCount)
        {
            string sql = string.Format("SELECT TOP {0} * FROM (SELECT * FROM MatchInfo " +
                " WHERE (HomeTeamId='{1}' OR VisitingTeamId='{1}') AND IsDeleted=0) AS t " + 
                " ORDER BY MatchDateTime DESC", matchCount, teamId);
            return NbaEntityHelper<MatchInfo>.GetEntitySet(sql);
        }

        public List<MatchInfo> GetMatchListByTeamId(Guid teamId)
        {
            string sql = string.Format("SELECT * FROM MatchInfo " +
                " WHERE (HomeTeamId='{0}' OR VisitingTeamId='{0}') AND IsDeleted=0 " +
                " ORDER BY MatchDateTime DESC", teamId);
            return NbaEntityHelper<MatchInfo>.GetEntitySet(sql);
        }

        public List<MatchInfo> GetMatchListByTeamId(Guid teamId, DateTime matchDateTime, int matchCount)
        {
            string sql = string.Format("SELECT TOP {2} * FROM (SELECT * FROM MatchInfo " +
                " WHERE (HomeTeamId='{0}' OR VisitingTeamId='{0}') AND MatchDateTime < '{1}' AND IsDeleted=0) AS t" +
                " ORDER BY MatchDateTime DESC", teamId, matchDateTime, matchCount);
            return NbaEntityHelper<MatchInfo>.GetEntitySet(sql);
        }

        public int Insert(MatchInfo matchInfo)
        {
            string sql = string.Format("INSERT INTO " +
                " MatchInfo(MatchId,SeasonYear,MatchDateTime,HomeTeamId,VisitingTeamId," +
                " HomeFirst,VisitingFirst,HomeSecond,VisitingSecond,HomeThird,VisitingThird,HomeFourth,VisitingFourth," +
                " HomeOt,VisitingOt,HomeScore,VisitingScore,OtCount) " +
                " VALUES(@MatchId,@SeasonYear,@MatchDateTime,@HomeTeamId,@VisitingTeamId," +
                " @HomeFirst,@VisitingFirst,@HomeSecond,@VisitingSecond,@HomeThird,@VisitingThird,@HomeFourth,@VisitingFourth," +
                " @HomeOt,@VisitingOt,@HomeScore,@VisitingScore,@OtCount)");
            SqlParameter[] SqlParameterSet = 
            {
                new SqlParameter("@MatchId", matchInfo.MatchId),
                new SqlParameter("@SeasonYear", matchInfo.SeasonYear),
                new SqlParameter("@MatchDateTime", matchInfo.MatchDateTime),
                new SqlParameter("@HomeTeamId", matchInfo.HomeTeamId),
                new SqlParameter("@VisitingTeamId", matchInfo.VisitingTeamId),
                new SqlParameter("@HomeFirst", matchInfo.HomeFirst),
                new SqlParameter("@VisitingFirst", matchInfo.VisitingFirst),
                new SqlParameter("@HomeSecond", matchInfo.HomeSecond),
                new SqlParameter("@VisitingSecond", matchInfo.VisitingSecond),
                new SqlParameter("@HomeThird", matchInfo.HomeThird),
                new SqlParameter("@VisitingThird", matchInfo.VisitingThird),
                new SqlParameter("@HomeFourth", matchInfo.HomeFourth),
                new SqlParameter("@VisitingFourth", matchInfo.VisitingFourth),
                new SqlParameter("@HomeOt", matchInfo.HomeOt),
                new SqlParameter("@VisitingOt", matchInfo.VisitingOt),
                new SqlParameter("@HomeScore", matchInfo.HomeScore),
                new SqlParameter("@VisitingScore", matchInfo.VisitingScore),
                new SqlParameter("@OtCount", matchInfo.OtCount)
            };

            return nbaDbUtility.ExecuteNonQuery(sql, SqlParameterSet);
        }

        public int Update(MatchInfo matchInfo)
        {
            string sql = "UPDATE MatchInfo SET SeasonYear=@SeasonYear," +
                " VisitingTeamId=@VisitingTeamId,HomeFirst=@HomeFirst,VisitingFirst=@VisitingFirst,HomeSecond=@HomeSecond," +
                " VisitingSecond=@VisitingSecond,HomeThird=@HomeThird,VisitingThird=@VisitingThird,HomeFourth=@VisitingThird," +
                " VisitingFourth=@VisitingFourth,HomeOt=@HomeOt,VisitingOt=@VisitingOt," +
                " HomeScore=@HomeScore,VisitingScore=@VisitingScore,OtCount=@OtCount,UpdateTime=@UpdateTime " +
                " WHERE MatchDateTime=@MatchDateTime AND MatchDateTime=@MatchDateTime AND HomeTeamId=@HomeTeamId";

            SqlParameter[] SqlParameterSet = 
            {
                new SqlParameter("@SeasonYear", matchInfo.SeasonYear),
                new SqlParameter("@MatchDateTime", matchInfo.MatchDateTime),
                new SqlParameter("@HomeTeamId", matchInfo.HomeTeamId),
                new SqlParameter("@VisitingTeamId", matchInfo.VisitingTeamId),
                new SqlParameter("@HomeFirst", matchInfo.HomeFirst),
                new SqlParameter("@VisitingFirst", matchInfo.VisitingFirst),
                new SqlParameter("@HomeSecond", matchInfo.HomeSecond),
                new SqlParameter("@VisitingSecond", matchInfo.VisitingSecond),
                new SqlParameter("@HomeThird", matchInfo.HomeThird),
                new SqlParameter("@VisitingThird", matchInfo.VisitingThird),
                new SqlParameter("@HomeFourth", matchInfo.HomeFourth),
                new SqlParameter("@VisitingFourth", matchInfo.VisitingFourth),
                new SqlParameter("@HomeOt", matchInfo.HomeOt),
                new SqlParameter("@VisitingOt", matchInfo.VisitingOt),
                new SqlParameter("@HomeScore", matchInfo.HomeScore),
                new SqlParameter("@VisitingScore", matchInfo.VisitingScore),
                new SqlParameter("@OtCount", matchInfo.OtCount),
                new SqlParameter("@UpdateTime", System.DateTime.Now)
            };

            return nbaDbUtility.ExecuteNonQuery(sql, SqlParameterSet);
        }

        public MatchInfo Find(DateTime matchDateTime, Guid visitingTeamId, Guid homeTeamId)
        {
            string sql = "SELECT * FROM MatchInfo m " +
                " LEFT JOIN TeamInfo v ON m.VisitingTeamId = v.TeamId " +
                " LEFT JOIN TeamInfo h ON m.HomeTeamId = h.TeamId " +
                " WHERE DATEDIFF(DAY,m.MatchDateTime,@MatchDateTime)=0 AND v.TeamId = @VisitingTeamId " +
                    " AND h.TeamId = @HomeTeamId";
            SqlParameter[] sqlParameterSet = 
            {
                new SqlParameter("@MatchDateTime", matchDateTime),
                new SqlParameter("@VisitingTeamId", visitingTeamId),
                new SqlParameter("@HomeTeamId", homeTeamId)
            };

            return NbaEntityHelper<MatchInfo>.GetEntity(sql, sqlParameterSet);
        }

        public Guid InsertOrUpdate(MatchInfo matchInfo)
        {
            Guid result = matchInfo.MatchId;

            MatchInfo tempMatchInfo = 
                Find(matchInfo.MatchDateTime, matchInfo.VisitingTeamId, matchInfo.HomeTeamId);

            if (tempMatchInfo == null)
            {
                matchInfo.MatchId = Guid.NewGuid();
                Insert(matchInfo);
                result = matchInfo.MatchId;
            }
            else
            {
                Update(matchInfo);
                result = tempMatchInfo.MatchId;
            }

            return result;
        }
    }
}
